package itesm.coler.data;

import itesm.coler.models.Word;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class MySQLWordDAO implements WordDAO {
	Connection connection = null;
	
	 public Connection getConnection(){
			if (connection == null)
			    connection = MySQLDAOFactory.createConnection();
			return connection;
		}
		
      public void setConnection(Connection connection) {
			this.connection = connection;
		}
		
	@Override
	public ArrayList<Word> findAll() {
		// TODO Auto-generated method stub
        PreparedStatement findAll = null;
        ResultSet results= null;
		ArrayList<Word> glossary = new ArrayList<Word>();
        connection = getConnection();
        if (connection!=null){
        		try {
        			findAll = connection.prepareStatement("select * from word");
        			results = findAll.executeQuery();
        			Word word = null;
        			while(results.next()){
        				word = mapResultSetToObject(results);	
        				glossary.add(word);	
        			}
        		}catch(SQLException e){
        			throw new DAOException(e);
        		} finally {
        			DAOUtil.close(connection,findAll,results);
        		}
        }
        return glossary;
	}
	
	
	public ArrayList<Word> findAllByProblem(Long problemId) throws DAOException {
	    // implement search words here using the supplied criteria.
	    // Alternatively, implement to return a Collection 
	    // of Transfer Objects.
         PreparedStatement findWords = null;
         ResultSet results= null;
		 ArrayList<Word> glossary = null;
		 try {
		       connection = getConnection();
		       findWords = connection.prepareStatement("SELECT word.* FROM word NATURAL JOIN problemword where problemid = ?");
		       synchronized(findWords) {
		    	   findWords.setLong(1,problemId);
		       // obtain list of words	  
		    	  results = findWords.executeQuery();
		       // get rows data{
		    	  glossary = new ArrayList<Word>();
		    	  Word word;
		          while (results.next()) {
		        	 word = mapResultSetToObject(results);		
		        	 glossary.add(word);	   
		         }
		       }
		   } catch (SQLException e){
			   throw new DAOException(e);
		   } finally {
				DAOUtil.close(connection,findWords, results);
		   }
		  return glossary;
	  }
	
	 public int insertWordByProblem(Word word, Long problemId) throws DAOException {
		 // Implement insert user here.
		    // Return 0 if correct
		    // or a -1 on error	  
		    PreparedStatement insert  = null;
		    int result = -1;
			  try {
				  connection = getConnection();
				  insert = connection.prepareStatement("insert into word(wordid,mainword,pluralform,synonym,abbreviation, active) values(?,?,?,?,?,?);");
				  Long wordId = word.getWordId();
				  insert.setLong(1, wordId);
				  insert.setString(2, word.getMainWord());
				  insert.setString(3, word.getPluralForm());
				  insert.setString(4, word.getSynonym());
				  insert.setString(5, word.getAbbreviation()+"");
				  insert.setInt(6, 1); //active
				  result = insert.executeUpdate();
				  insert = connection.prepareStatement("insert into problemword(problemId,wordid) values(?,?);");
				  insert.setLong(1,problemId);
				  insert.setLong(2,wordId); 		 
				  result = insert.executeUpdate();
				  insert.close();
				  connection.close();
			} catch (SQLException e) {
				  throw new DAOException(e);
	        } finally {
	              DAOUtil.close(connection, insert);
	        }
			return result;
	 }

	
	 public Word mapResultSetToObject(ResultSet results) throws SQLException {
		  System.out.println("Voy a mapear el objeto word");
		  Word word = new Word();
		  word.setWordId(results.getLong("wordid"));
	      word.setAbbreviation(results.getString("abbreviation"));
		  word.setMainWord(results.getString("mainword"));
		  word.setPluralForm(results.getString("pluralform"));
		  word.setSynonym(results.getString("synonym"));
		  return word;
	 }
}
